<?php
/**
 * 検索 共通クラス<br />
 *
 * @package apamanshop
 * @author SS永田
 * @copyright コピーライト(要確認)
 */
class Search_Model_Common extends Search_Model_Base
{
    /**
    *  パラメータの存在有無と、値が入っているかを判定する関数
    */
	public function checkvalue($params, $params_name)
	{
		$retvalue = false;

		if(!empty($params[$params_name]) && isset($params[$params_name]))	// パラメータ存在有り && 値が入っているの場合
		{
			$retvalue =  true;
		}

		return	$retvalue;
	}

	/**
	* データが存在すればその値を返し、NULLの場合は空を返す
	*
	* @access	public
	* @param	array	連想配列
	* @param	string	配列キー
	* @return	string
	*/
	public function returnValue($params, $params_name)
	{
		$result = "";

		// データが存在すればその値を返す
		if(array_key_exists($params_name, $params)) $result = $params[$params_name];

		return	$result;
	}

	/**
	* 検索条件名を返す
	*
	* @access   public
	* @param    array   $params      検索パラメータ
	* @param    int     $form_flg    検索条件接続フラグ
	* @param    string  $sepa_cd     結合記号
	*/
	public function getFormSearchName($params, $form_flg = 0, $sepa_cd = '')
	{
		$return = array();

		// 指定パラメータが存在する場合、取得処理を行う
		if($params['PrefCd']) $return = $this->getTodohukenName($params, $return);
// TODO		if($params['SiteiCd']) $this->getTodohukenName($params);
		if($params['CityCd']) $return = $this->getSikucyosonName($params, $return);
		if($params['TownCd']) $return = $this->getCyoName($params, $return);

		if($params['RailCd']) $return = $this->getEnsenName($params, $return);
		if($params['StCd'])   $return = $this->getEkiName($params, $return);

		if($params['station'])$return = $this->getRosenzuEkiName($params, $return);

		// 都道府県にひもつくデータをグループ化する
		if($form_flg == 1){

			$name_params = array();

// TODO
/*
 *
 * 検索導線として、この機能を必要としないものは以下
 *
 * 地図検索
 * 情報誌No検索
 * 学校名検索
 * マイページ検索条件登録検索
 * マイページ検索条件変更検索
 * 物件リクエスト
 * 社宅物件リクエスト
 *
 */

			switch($params['SrhKbn']){

				/*
				 * 住所検索
				 * こだわり物件検索
				 * 特集検索
				 * キャンペーン検索
				 * 住居形態検索
				 */
				case Apm_Config_Search::SRHKBN_JYUSYO:
				case Apm_Config_Search::SRHKBN_KODAWARI:
				case Apm_Config_Search::SRHKBN_TOKUSYU:
				case Apm_Config_Search::SRHKBN_CAMPAIGN:
				case Apm_Config_Search::SRHKBN_JYUKYOKEITAI:

					// 町コードが存在する場合
					if($return['TownCd']){
						$name_params = $this->getFormNameToTown($sepa_cd, $return);
					}
					// 町コードが存在しない場合
					elseif($return['CityCd']){
						$name_params = $this->getFormNameToCity($sepa_cd, $return);
					}

				break;

				/*
				 * 沿線・駅検索
				 * 住みたい駅検索
				 *
				 */
				case Apm_Config_Search::SRHKBN_ENSENEKI:
				case Apm_Config_Search::SRHKBN_SUMITAIEKI:

					// 駅コードが存在する場合
					if($return['StCd']){
						$name_params = $this->getFormNameToSt($sepa_cd, $return);
					}
					// 駅コードが存在しない場合
					elseif($return['RailCd']){
						$name_params = $this->getFormNameToRail($sepa_cd, $return);
					}

				break;

				/*
				 * 路線図検索
				 */
				case Apm_Config_Search::SRHKBN_ROSENZU:
					if($return['StCd']){
						$name_params = $this->getFormNameToRosenzuSt($sepa_cd, $return);
					}
				break;

				//家賃相場検索
				case Apm_Config_Search::SRHKBN_YACHINSOUBA:
				//賃料×間取り検索
				case Apm_Config_Search::SRHKBN_CHINRYOMADORI:
				case Apm_Config_Search::SRHKBN_MYPAGE_ADD:
				case Apm_Config_Search::SRHKBN_MYPAGE_UPD:
				case Apm_Config_Search::SRHKBN_BUKENREQUEST:
				case Apm_Config_Search::SRHKBN_SYATAKU:
				case Apm_Config_Search::SRHKBN_BUKENREQUEST_SYATAKU:
					switch($params['SrhType']){
						case Apm_Config_Search::SRHTYPE_JYUSYO:
							// 町コードが存在する場合
							if($return['TownCd']){
								$name_params = $this->getFormNameToTown($sepa_cd, $return);
							}
							// 町コードが存在しない場合
							elseif($return['CityCd']){
								$name_params = $this->getFormNameToCity($sepa_cd, $return);
							}
						break;
						case Apm_Config_Search::SRHTYPE_ENSENEKI:
							// 駅コードが存在する場合
							if($return['StCd']){
								$name_params = $this->getFormNameToSt($sepa_cd, $return);
							}
							// 駅コードが存在しない場合
							elseif($return['RailCd']){
								$name_params = $this->getFormNameToRail($sepa_cd, $return);
							}
						break;

						default:
						break;
					}
				break;

				default:


				break;

			}

			// クラス変数にセット
			$return['search_name'] = $name_params;
		}

		return $return;

	}

	/**
	* 名前を「町」まで整形する
	*    例）栃木県-佐野市-赤坂町
	*
	* @access	public
	* @param    string   $sepa_cd      接続記号
	* @return	array    $name_params  整形済みの名前
	*/
	public function getFormNameToTown($sepa_cd, $return){

		$name_params = array();

		foreach($return['PrefCd'] as $pref_key => $pref_val){
			foreach($return['CityCd'] as $city_key => $city_val){
				foreach($return['TownCd'] as $town_key => $town_val){

					// コード切り取り
					$form_pref_city_key = substr($city_key, 0, 2);
					$form_pref_town_key = substr($town_key, 0, 2);
					$form_city_city_key = substr($city_key, 2, 3);
					$form_city_town_key = substr($town_key, 2, 3);

					if(   $pref_key == $form_pref_city_key
					   && $pref_key == $form_pref_town_key
					   && $form_city_city_key == $form_city_town_key){

						$search_word = "";
						$search_word .= $pref_val['PREF_NAME'];
						$search_word .= $sepa_cd;
						$search_word .= $city_val['CITY_NAME'];
						$search_word .= $sepa_cd;
						$search_word .= $town_val['TOWN_NAME'];

						$name_params[] = $search_word;
					}
				}
			}
		}

		return $name_params;
	}

	/**
	* 名前を「市区町村」まで整形する
	*    例）栃木県-佐野市
	*
	* @access	public
	* @param    string   $sepa_cd      接続記号
	* @return	array    $name_params  整形済みの名前
	*/
	static function getFormNameToCity($sepa_cd, $return){

		$name_params = array();

		foreach($return['PrefCd'] as $pref_key => $pref_val){
			foreach($return['CityCd'] as $city_key => $city_val){

				// コード切り取り
				$form_pref_city_key = substr($city_key, 0, 2);
				$form_pref_town_key = substr($town_key, 0, 2);

				if($pref_key == $form_pref_city_key){

					$search_word = "";
					$search_word .= $pref_val['PREF_NAME'];
					$search_word .= $sepa_cd;
					$search_word .= $city_val['CITY_NAME'];

					$name_params[] = $search_word;
				}
			}
		}

		return $name_params;
	}

	/**
	* 名前を「町」まで整形する
	*    例）栃木県-佐野市-赤坂町
	*
	* @access	public
	* @param    string   $sepa_cd      接続記号
	* @return	array    $name_params  整形済みの名前
	*/
	public function getFormNameToSt($sepa_cd, $return){

		$name_params = array();

		foreach($return['PrefCd'] as $pref_key => $pref_val){
			foreach($return['RailCd'] as $rail_key => $rail_val){
				foreach($return['StCd'] as $st_key => $st_val){

					// コード切り取り
					$form_pref_rail_key = substr($rail_key, 0, 2);
					$form_pref_st_key = substr($st_key, 0, 2);
					$form_rail_rail_key = substr($rail_key, 2, 3);
					$form_rail_st_key = substr($st_key, 2, 3);

					if(   $pref_key == $form_pref_rail_key
					   && $pref_key == $form_pref_st_key
					   && $form_rail_rail_key == $form_rail_st_key){

						$search_word = "";
						$search_word .= $pref_val['PREF_NAME'];
						$search_word .= $sepa_cd;
						$search_word .= $rail_val['RAIL_NAME'];
						$search_word .= $sepa_cd;
						$search_word .= $st_val['ST_NAME'];

						$name_params[] = $search_word;
					}
				}
			}
		}

		return $name_params;
	}

	/**
	* 名前を「市区町村」まで整形する
	*    例）栃木県-佐野市
	*
	* @access	public
	* @param    string   $sepa_cd      接続記号
	* @return	array    $name_params  整形済みの名前
	*/
	static function getFormNameToRail($sepa_cd, $return){

		$name_params = array();

		foreach($return['PrefCd'] as $pref_key => $pref_val){
			foreach($return['RailCd'] as $rail_key => $rail_val){

				// コード切り取り
				$form_pref_rail_key = substr($rail_key, 0, 2);

				if($pref_key == $form_pref_rail_key){

					$search_word = "";
					$search_word .= $pref_val['PREF_NAME'];
					$search_word .= $sepa_cd;
					$search_word .= $rail_val['RAIL_NAME'];

					$name_params[] = $search_word;
				}
			}
		}

		return $name_params;
	}

	/**
	* 名前を「駅」まで整形する
	*    例）東京都新宿
	*
	* @access	public
	* @param    string   $sepa_cd      接続記号
	* @return	array    $name_params  整形済みの名前
	*/
	static function getFormNameToRosenzuSt($sepa_cd, $return){

		$name_params = array();

		foreach($return['PrefCd'] as $pref_key => $pref_val){
			foreach($return['StCd'] as $st_key => $st_val){

				// コード切り取り
				$form_pref_st_key = substr($st_key, 0, 2);
				$form_rail_st_key = substr($st_key, 2, 3);

				if( $pref_key == $form_pref_st_key && $tempStName != $st_val['ST_NAME']){

					$search_word = "";
					$search_word .= $pref_val['PREF_NAME'];
					$search_word .= $sepa_cd;
					$search_word .= $st_val['ST_NAME'];

					if (!in_array($search_word, $name_params)) {
						$name_params[] = $search_word;
						$tempStName = $st_val['ST_NAME'];
					}
				}
			}
		}

		return $name_params;
	}

	/**
	* 都道府県名を返す
	*/
	public function getTodohukenName($params, $return)
	{

		$db_params = array();

		$sql = $this->generatePrefSql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);
		$return = $this->convertJyusyoData($result, $return);

		return $return;
	}

	/**
	 *  都道府県CDからデータ取得用SQL
	 */
	public function generatePrefSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "FROM ";
		$sql .= "     M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        " . $this->createWhere('M43.M43_TODOUFUKEN_CD', $params['PrefCd']) . " ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";

		return $sql;
	}

	/**
	 *  住所データコンバート
	 */
	public function convertJyusyoData($result, $data)
	{
		// 初期化
		if (!isset($data['PrefCd']))  $data['PrefCd']  = array();
		if (!isset($data['SiteiCd'])) $data['SiteiCd'] = array();
		if (!isset($data['CityCd']))  $data['CityCd']  = array();
		if (!isset($data['TownCd']))  $data['TownCd']  = array();

		// 結果ループ
		foreach ($result as $jyusyo)
		{
			// 都道府県
			$prefCd = $jyusyo['TODOUFUKEN_CD'];
			if (!empty($prefCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($prefCd, $data['PrefCd']) == false)
				{
					$data['PrefCd'][$prefCd]['PREF_NAME'] = $jyusyo['TODOUFUKEN'];
				}
			}
			// 政令指定都市
			$siteiCd = $jyusyo['SITEITOSI_CD'];
			if (!empty($siteiCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($siteiCd, $data['SiteiCd']) == false)
				{
//					$data['SiteiCd'][$siteiCd]['SITEI_NAME'] = $jyusyo['SITEITOSI'];
					$data['SiteiCd'][$prefCd.$siteiCd]['SITEI_NAME'] = $jyusyo['SITEITOSI'];
				}
			}
			// 市区町村
			$sikuCd = $jyusyo['SHIKUCHOUSON_CD'];
			if (!empty($sikuCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($sikuCd, $data['CityCd']) == false)
				{
//					$data['CityCd'][$sikuCd]['CITY_NAME'] = $jyusyo['SHIKUCHOUSON'];
					$data['CityCd'][$prefCd.$sikuCd]['CITY_NAME'] = $jyusyo['SHIKUCHOUSON'];
				}
			}
			// 町名
			$cyoCd = $jyusyo['CHOUMEI_CD'];
			if (!empty($cyoCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($cyoCd, $data['TownCd']) == false)
				{
//					$data['TownCd'][$cyoCd]['TOWN_NAME'] = $jyusyo['CHOUMEI'];
					$data['TownCd'][$prefCd.$sikuCd.$cyoCd]['TOWN_NAME'] = $jyusyo['CHOUMEI'];
				}
			}
		}

		return $data;
	}

	/**
	* 市区町村名を返す
	*/
	public function getSikucyosonName($params, $return)
	{

		$db_params = array();

		$sql = $this->generateCitySql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);
		$return = $this->convertJyusyoData($result, $return);

		return $return;
	}

	/**
	 *  市区町村町CDからデータ取得用SQL
	 */
	public function generateCitySql($params, &$db_params)
	{

		// WHERE句生成
		$strWhere = "";
		for ($i = 0; $i < count($params['CityCd']); $i++)
		{
			// OR句の取得
			$strOr = ($i != 0) ? " OR" : "";
			// パラメータ
			$db_params['strCityCd_'.$i] = $params['CityCd'][$i];

			$strWhere .= $strOr." ( M01.M01_TODOUFUKEN_CD = substr( :strCityCd_".$i." ,1,2)";
			$strWhere .= " AND M01.M01_SHIKUCHOUSON_CD    = substr( :strCityCd_".$i." ,3,3))";
		}

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M01.M01_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_CD as SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON as SHIKUCHOUSON ";
		$sql .= "    ,M44.M44_SITEITOSI_CD as SITEITOSI_CD ";
		$sql .= "    ,M44.M44_SITEITOSI as SITEITOSI ";
		$sql .= "FROM ";
		$sql .= "     M01JYUUSHO M01 ";
		$sql .= "    ,M44SITEITOSI M44 ";
		$sql .= "    ,M44SITEITOSI_SIKU M44_SIKU ";
		$sql .= "WHERE ";
		$sql .= "        trim(M01_ABOLISH_YM) is null ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M44_SIKU.M44_TODOUFUKEN_CD(+) ";
		$sql .= "    AND M01.M01_SHIKUCHOUSON_CD = M44_SIKU.M44_SHIKUCHOUSON_CD(+) ";
		$sql .= "    AND M44_SIKU.M44_SITEITOSI_CD = M44.M44_SITEITOSI_CD(+) ";
		$sql .= "    AND (" . $strWhere . ") ";
		$sql .= "GROUP BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";
		$sql .= "    ,M44.M44_SITEITOSI_CD ";
		$sql .= "    ,M44.M44_SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "ORDER BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";

		return $sql;
	}

	/**
	* 町名を返す
	*/
	public function getCyoName($params, $return)
	{

		$db_params = array();

		$sql = $this->generateTownSql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);
		$return = $this->convertJyusyoData($result, $return);

		return $return;
	}

	/**
	 *  町CDからデータ取得用SQL
	 */
	public function generateTownSql($params, &$db_params)
	{

		// WHERE句生成
		$strWhere = "";
		for ($i = 0; $i < count($params['TownCd']); $i++)
		{
			// OR句の取得
			$strOr = ($i != 0) ? " OR" : "";
			// パラメータ
			$db_params['strTownCd_'.$i] = $params['TownCd'][$i];

			$strWhere .= $strOr." ( M01.M01_TODOUFUKEN_CD = substr( :strTownCd_".$i." ,1,2)";
			$strWhere .= " AND M01.M01_SHIKUCHOUSON_CD    = substr( :strTownCd_".$i." ,3,3)";
			$strWhere .= " AND M01.M01_CHOUMEI_CD         = substr( :strTownCd_".$i." ,6,3))";
		}

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M01.M01_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_CD as SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_CHOUMEI_CD as CHOUMEI_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON as SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_CHOUMEI as CHOUMEI ";
		$sql .= "    ,M44.M44_SITEITOSI_CD as SITEITOSI_CD ";
		$sql .= "    ,M44.M44_SITEITOSI as SITEITOSI ";
		$sql .= "FROM ";
		$sql .= "     M01JYUUSHO M01 ";
		$sql .= "    ,M44SITEITOSI M44 ";
		$sql .= "    ,M44SITEITOSI_SIKU M44_SIKU ";
		$sql .= "WHERE ";
		$sql .= "        trim(M01_ABOLISH_YM) is null ";
		$sql .= "    AND M01.M01_TODOUFUKEN_CD = M44_SIKU.M44_TODOUFUKEN_CD(+) ";
		$sql .= "    AND M01.M01_SHIKUCHOUSON_CD = M44_SIKU.M44_SHIKUCHOUSON_CD(+) ";
		$sql .= "    AND M44_SIKU.M44_SITEITOSI_CD = M44.M44_SITEITOSI_CD(+) ";
		$sql .= "    AND (" . $strWhere . ") ";
		$sql .= "GROUP BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_CD ";
		$sql .= "    ,M01.M01_CHOUMEI_CD ";
		$sql .= "    ,M01.M01_TODOUFUKEN ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON ";
		$sql .= "    ,M01.M01_CHOUMEI ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";
		$sql .= "    ,M01.M01_CHOUMEI_KANA ";
		$sql .= "    ,M44.M44_SITEITOSI_CD ";
		$sql .= "    ,M44.M44_SITEITOSI ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "ORDER BY ";
		$sql .= "     M01.M01_TODOUFUKEN_CD ";
		$sql .= "    ,M44.M44_SITEITOSI_KANA ";
		$sql .= "    ,M01.M01_SHIKUCHOUSON_KANA ";
		$sql .= "    ,M01.M01_CHOUMEI_KANA ";

		return $sql;
	}

	/**
	 *  駅CDからデータ取得用SQL
	 */
	public function generateStSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M02.M02_ENSEN_CD as ENSEN_CD ";
		$sql .= "    ,M02.M02_ENSEN_NAME as ENSEN_NAME ";
		$sql .= "    ,M03.M03_EKI_CD as EKI_CD ";
		$sql .= "    ,M03.M03_EKI_NAME as EKI_NAME ";
		$sql .= "FROM ";
		$sql .= "     M03EKI M03 ";
		$sql .= "    ,M02ENSEN M02 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        M03.M03_ENSEN_CD = M02.M02_ENSEN_CD ";
		$sql .= "    AND M03.M03_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "    AND " . $this->createWhere('M03.M03_EKI_CD', $params['StCd']) . " ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M02.M02_ENSEN_KUBUN ";
		$sql .= "    ,M02.M02_ENSEN_KANA ";
		$sql .= "    ,M03.M03_EKI_CD ";

		return $sql;
	}

	/**
	 *  路線図駅CDからデータ取得用SQL
	 */
	public function generateRosenzuStSql($params, &$db_params)
	{
		$sql  = "";
		$sql .= "SELECT DISTINCT";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M03.M03_EKI_CD as EKI_CD ";
		$sql .= "    ,M03.M03_EKI_NAME as EKI_NAME ";
		$sql .= "FROM ";
		$sql .= "    M43TODOUFUKEN M43 ";
		$sql .= "    ,M03EKI M03 ";
		$sql .= "WHERE ";
		$sql .=       $this->createWhere('M03_ROSENZU_EKI_CD', $params['StCd']);
		$sql .= "     AND M03.M03_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M03.M03_EKI_CD ";

		return $sql;
	}

	/**
	 *  沿線CDからデータ取得用SQL
	 */
	public function generateRailSql($params, &$db_params)
	{

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M02.M02_ENSEN_CD as ENSEN_CD ";
		$sql .= "    ,M02.M02_ENSEN_NAME as ENSEN_NAME ";
		$sql .= "FROM ";
		$sql .= "     M04ENSEN_AREA M04 ";
		$sql .= "    ,M02ENSEN M02 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        M04.M04_ENSEN_CD = M02.M02_ENSEN_CD ";
		$sql .= "    AND M04.M04_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";

		if(isset($params['PrefCd'])){
			$sql .= "    AND " . $this->createWhere('M04.M04_TODOUFUKEN_CD', $params['PrefCd']) . " ";
		}
		$sql .= "    AND " . $this->createWhere('M04.M04_ENSEN_CD', $params['RailCd']) . " ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ,M02.M02_ENSEN_KUBUN ";
		$sql .= "    ,M02.M02_ENSEN_KANA ";

		return $sql;
	}

	/**
	 *  沿線・駅データコンバート
	 */
	public function convertEnsenekiData($result, $data)
	{
		// 初期化
		if (!isset($data['PrefCd'])) $data['PrefCd']  = array();
		if (!isset($data['RailCd'])) $data['RailCd']  = array();
		if (!isset($data['StCd']))   $data['StCd'] = array();

		// 結果ループ
		foreach ($result as $rail)
		{
			// 都道府県
			$prefCd = $rail['TODOUFUKEN_CD'];
			if (!empty($prefCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($prefCd, $data['PrefCd']) == false)
				{
					$data['PrefCd'][$prefCd]['PREF_NAME'] = $rail['TODOUFUKEN'];
				}
			}
			// 沿線
			$ensenCd = $rail['ENSEN_CD'];
			if (!empty($ensenCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($ensenCd, $data['RailCd']) == false)
				{
					$data['RailCd'][$prefCd.$ensenCd]['RAIL_NAME'] = $rail['ENSEN_NAME'];
				}
			}
			// 駅
			$ekiCd = $rail['EKI_CD'];
			if (!empty($ekiCd))
			{
				// 重複しない場合、値セット
				if (array_key_exists($ekiCd, $data['StCd']) == false)
				{
					$data['StCd'][$prefCd.$ensenCd.$ekiCd]['ST_NAME'] = $rail['EKI_NAME'];
				}
			}
		}

		return $data;
	}

	/**
	* 沿線名を返す
	*/
	public function getEnsenName($params, $return)
	{
		$db_params = array();

		$sql = $this->generateRailSql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);
		$return = $this->convertEnsenekiData($result, $return);
		return $return;
	}

	/**
	* 駅名を返す
	*/
	public function getEkiName($params, $return)
	{
		$db_params = array();

		$sql = $this->generateStSql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);
		$return = $this->convertEnsenekiData($result, $return);

		return $return;
	}

	/**
	* 駅名(路線図)を返す
	*/
	public function getRosenzuEkiName($params, $return)
	{

		$db_params = array();

		$params['StCd'] = array_diff($params['stcode'],array(""));

		$sql = $this->generateRosenzuStSql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);
		$return = $this->convertEnsenekiData($result, $return);

		return $return;
	}

}
